package gis.serviceImpl;

import bean.cityfun.AreaDimSceneType;
import bean.common.CellDimScene;
import bean.common.CellRefDimScene;
import bean.common.CellRefDimSceneExpot;
import gis.common.ServiceFactory;
import gis.core.bean.KeyValue;
import gis.service.CellSceneService;
import gis.sqlbase.DatabaseManage;
import gis.sqlbase.DatabaseName;
import gis.sqlbase.SQLHelper;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * Created by hupeng on 16/6/7.
 */
public class CellSceneServiceImpl extends CellSceneService {

    private static final SQLHelper db = DatabaseManage.Instance(DatabaseName.common);
    private static Logger logger = LoggerFactory.getLogger(ServiceFactory.class);

    @Override
    public List<AreaDimSceneType> GetSceneTypeList() throws Exception {
        List<AreaDimSceneType> list = new ArrayList<AreaDimSceneType>();
        String sql = "SELECT scene_type_id,scene_type_name from ads_f_area_dim_scene_type";
        ResultSet rs = db.getResultSet(sql);
        try {
            while (rs.next()) {
                AreaDimSceneType item = new AreaDimSceneType();
                item.setScene_type_id(rs.getString("scene_type_id"));
                item.setScene_type_name(rs.getString("scene_type_name"));
                list.add(item);
            }
        } catch (Exception e) {
            logger.error("查询用户自定义场景分类出错：" + e.getMessage(), e);
        } finally {
            db.close(rs);
        }

        return list;
    }

    @Override
    public List<KeyValue> GetSceneAppTopics() throws Exception {
        return null;
    }

    @Override
    public int AddSceneTypes(List<AreaDimSceneType> sceneTypes) throws Exception {
        String sql = "INSERT INTO ads_f_area_dim_scene_type (scene_type_id,scene_type_name) VALUES";

        List<String> values = new ArrayList<String>();
        for (AreaDimSceneType item : sceneTypes) {
            values.add("(" + item.getScene_type_id() + ",'" + item.getScene_type_name() + ")");
        }

        sql += StringUtils.join(values, ',');

        return db.ExecSql(sql);
    }

    @Override
    public int DeleteCellRefScene(String scene_id) throws Exception {
        String sql = "DELETE FROM  ads_cell_ref_scene WHERE scene_id=" + scene_id;
        return db.ExecSql(sql);
    }

    @Override
    public List<CellRefDimSceneExpot> GetLExpotData(String sceneTypeId, String cityId, String appTopicsId) throws Exception {
        List<CellRefDimSceneExpot> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer(" select a.scene_id,a.scene_name,a.scene_type_id,a.scene_type_name,a.cell_id,a.cell_name," +
                "a.tac,a.eci,a.cell_type,b.city_id,b.city_name,a.longitude,a.latitude,a.azimuth\n" +
                "  from ads_cell_ref_scene a left join ads_dim_scene b  on a.scene_id = b.scene_id where 2>1 ");

        if (StringUtils.isNotEmpty(sceneTypeId) && !sceneTypeId.equals("0")) {
            sql.append(" and scene_type_id = " + sceneTypeId);
        }
        if (StringUtils.isNotEmpty(cityId) && !cityId.equals("0")) {
            sql.append(" and city_id = " + cityId);
        }
        ResultSet rs = db.getResultSet(sql.toString());

        try {
            while (rs.next()) {
                CellRefDimSceneExpot item = new CellRefDimSceneExpot();
                item.setScene_id(rs.getString("scene_id"));
                item.setScene_name(rs.getString("scene_name"));
                item.setScene_type_id(rs.getString("scene_type_id"));
                item.setScene_type_name(rs.getString("scene_type_name"));
                item.setId(rs.getString("cell_id"));
                item.setName(rs.getString("cell_name"));
                item.setTac(rs.getString("tac"));
                item.setEci(rs.getString("eci"));
                item.setStrCellType(rs.getString("cell_type"));
                item.setCityId(rs.getString("city_id"));
                item.setCityName(rs.getString("city_name"));
                item.setLongitude(rs.getDouble("longitude"));
                item.setLatitude(rs.getDouble("latitude"));
                item.setAzimuth(rs.getDouble("azimuth"));
                list.add(item);
            }
        } catch (Exception e) {
            logger.error("查询导出场景出错：" + e.getMessage(), e);
            throw e;
        } finally {
            db.close(rs);
        }

        return list;
    }

    @Override
    public int AddCellDimScene(CellDimScene item) throws Exception {
        String sql = "INSERT INTO ads_dim_scene(scene_id,scene_name,scene_type_id, scene_type_name, city_id,city_name,cell_types,\"range\")" +
                "VALUES(" + item.getScene_id() + ",'" + item.getScene_name() + "'" +
                ",'" + item.getScene_type_id() + "','" + item.getScene_type_name() + "'," + item.getCityId() + ",'" + item.getCityName() + "','" + item.getCellTypes() + "','" + item.getRange() + "')";

        return db.ExecSql(sql);
    }

    @Override
    public int AddCellDimScenes(Collection<CellDimScene> scenes) throws Exception {
        String sql = "INSERT INTO ads_dim_scene(scene_id,scene_name,scene_type_id, scene_type_name, `range`,city_id,city_name,cell_types) VALUES";

        List<String> values = new ArrayList<String>();
        for (CellDimScene item : scenes) {
            values.add("(" + item.getScene_id() + ",'" + item.getScene_name() + "'" +
                    ",'" + item.getScene_type_id() + "','" + item.getScene_type_name() +
                    "','" + item.getRange() + "'," + item.getCityId() + ",'" + item.getCityName() + "','" + item.getCellTypes() + "')");
        }
        sql += StringUtils.join(values, ',');

        return db.ExecSql(sql);
    }

    @Override
    public int DeleteCellDimScene(String id) throws Exception {
        String sql = "DELETE FROM  ads_dim_scene WHERE scene_id=" + id;
        return db.ExecSql(sql);
    }

    @Override
    public List<CellDimScene> GetCellSceneList(String sceneTypeId, String cityId, String apptopicsid) throws Exception {
        List<CellDimScene> list = new ArrayList<CellDimScene>();
        String sql = "SELECT scene_id,scene_name,scene_type_id, scene_type_name,`range`,city_id,city_name,cell_types FROM ads_dim_scene where 2>1 ";
        if (StringUtils.isNotEmpty(sceneTypeId) && !sceneTypeId.equals("0")) {
            sql += " and scene_type_id = " + sceneTypeId;
        }
        if (StringUtils.isNotEmpty(cityId) && !cityId.equals("0")) {
            sql += " and city_id = " + cityId;
        }
        ResultSet rs = db.getResultSet(sql);

        try {
            while (rs.next()) {
                CellDimScene item = new CellDimScene();
                item.setScene_id(rs.getString("scene_id"));
                item.setScene_name(rs.getString("scene_name"));
                item.setScene_type_id(rs.getString("scene_type_id"));
                item.setScene_type_name(rs.getString("scene_type_name"));
                item.setRange(rs.getString("range"));
                item.setCityId(rs.getString("city_id"));
                item.setCityName(rs.getString("city_name"));
                item.setCellTypes(rs.getString("cell_types"));
                list.add(item);
            }
        } catch (Exception e) {
            logger.error("查询场景出错：" + e.getMessage(), e);
        } finally {
            db.close(rs);
        }

        return list;
    }

    @Override
    public int AddCellRefSceneList(List<CellRefDimScene> list) throws Exception {
        String sql = "INSERT INTO ads_cell_ref_scene" +
                "(scene_id,scene_name,scene_type_id,scene_type_name,cell_id,cell_name," +
                "tac,eci,cell_type,longitude,latitude,azimuth)VALUES";

        List<String> values = new ArrayList<String>();
        for (CellRefDimScene item : list) {
            values.add("(" + item.getScene_id() + ",'" + item.getScene_name() + "'," +
                    "'" + item.getScene_type_id() + "','" + item.getScene_type_name() + "'," +
                    "'" + item.getId() + "','" + item.getName() + "'," +
                    "'" + item.getTac() + "','" + item.getEci() + "','" + item.getStrCellType() + "',"
                    + item.getLongitude() + "," + item.getLatitude() + "," + item.getAzimuth() + ")");
        }

        sql += StringUtils.join(values, ',');

        return db.ExecSql(sql);
    }

    @Override
    public List<CellRefDimScene> GetCellRefSceneList(String scene_id) throws Exception {
        List<CellRefDimScene> list = new ArrayList<CellRefDimScene>();
        String sql = "SELECT scene_id,scene_name,scene_type_id,scene_type_name,cell_id,cell_name,tac,eci,cell_type,longitude,latitude,azimuth FROM ads_cell_ref_scene WHERE scene_id=" + scene_id;
        ResultSet rs = db.getResultSet(sql);

        try {
            while (rs.next()) {
                CellRefDimScene item = new CellRefDimScene();
                item.setScene_id(rs.getString("scene_id"));
                item.setScene_name(rs.getString("scene_name"));
                item.setScene_type_id(rs.getString("scene_type_id"));
                item.setScene_type_name(rs.getString("scene_type_name"));
                item.setId(rs.getString("cell_id"));
                item.setName(rs.getString("cell_name"));
                item.setTac(rs.getString("tac"));
                item.setEci(rs.getString("eci"));
                item.setLongitude(rs.getDouble("longitude"));
                item.setLatitude(rs.getDouble("latitude"));
                item.setStrCellType(rs.getString("cell_type"));
                item.setAzimuth(rs.getDouble("azimuth"));

                item.setBtsId(item.getLongitude() + "" + item.getLatitude());
                list.add(item);
            }
        } catch (Exception e) {
            logger.error("查询场景关联小区出错：" + e.getMessage(), e);
        } finally {
            db.close(rs);
        }

        return list;
    }

}
